package com.zsl.dao.impl;

import com.zsl.dao.CategoryDao;
import com.zsl.model.Category;
import com.zsl.util.C3P0Util;
import com.zsl.util.DbHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CategoryDaoImpl implements CategoryDao {

    @Override
    public String getCategoryCount(String[] whereColumn, String[] whereValue) throws SQLException {
        String count = null;
        StringBuilder sql = null;
        sql = new StringBuilder("select count(*) from category where 1=1");
        if (whereColumn != null) {
            for (String wc : whereColumn) {
                sql.append(" AND ").append(wc).append(" =?");
            }
        }
        ResultSet rs = DbHelper.executeQuery(sql.toString(),whereValue);
        while(rs.next()){
            count=rs.getString(1);
        }
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        return count;
    }

    @Override
    public List<Category> getCategory(String[] whereColumn, String[] whereValue) throws SQLException {
        StringBuilder sql = null;
        List<Category> categories = new ArrayList<>();

        sql = new StringBuilder("select * from category");
        if (whereColumn != null) {
            for (String wc : whereColumn) {
                sql.append(" AND ").append(wc).append(" =?");
            }
        }
        ResultSet rs = DbHelper.executeQuery(sql.toString(), whereValue);
        while (rs.next()){
            Category category = new Category();
            category.setId(rs.getString("id"));
            category.setName(rs.getString("name"));
            category.setDescription(rs.getString("description"));
            categories.add(category);
        }
        //关闭连接
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        return categories;
    }

    @Override
    public Map<String, Object> insertCategory(List<Category> categories) {
        String sql = "insert into category (id,name,description,is_deleted) values(?,?,?,'0')";
        List<Integer> line = new ArrayList<>();
        for(Category category:categories){
            line.add(DbHelper.executeUpdate(sql,new Object[]{category.getId(),category.getName(),category.getDescription()}));
        }
        Map<String,Object> result = new HashMap<>();
        result.put("line",line);
        return result;
    }

    @Override
    public Map<String, Object> updateCategoryById(List<Category> categories, int selectModel) {
        List<String> category_id = new ArrayList<>();
        Map<String,Object> result = new HashMap<>();
        List<Integer> line = new ArrayList<>();
        //update name,description
        if(selectModel==1){
            for(Category category:categories){
                category_id.add(category.getId());
                line.add(DbHelper.update("category",new String[]{"name","description"},
                        new String[]{category.getName().toString(),category.getDescription().toString()},
                        new String[]{"id"},
                        new String[]{category.getId()}));
            }
            result.put("line",line);
            result.put("category_id",category_id);
        }
        return result;
    }

    @Override
    public Integer deleteCategoryById(String category_id) {
        String sql="delete from category where id = ?";
        return DbHelper.executeDelete(sql,new Object[]{category_id});
    }

    @Override
    public boolean isExistCategory(String name) throws SQLException {
        String flag = null;
        String sql = "select count(*) from category where name = ?";
        String[] params = {name};
        ResultSet rs = DbHelper.executeQuery(sql,params);
        if(rs.next()){
            flag = rs.getString(1);
        }
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        if(flag.equals("1")){
            return true;
        }else
            return false;
    }
}
